package com.cfs.controller;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;

import com.cfs.entity.HistoricalData;
import com.shuohe.service.EasyUiDTO;
import com.shuohe.util.db.DbQuerySql;
import com.shuohe.util.easyuiBean.EasyUiDataGrid;

/**
 * 历史数据分析的操作类
 * @author zz
 *
 */
@Controller  
@RequestMapping("/history/data/analysis*")  
public class ActionHistoricalData
{
	 @Autowired
     private JdbcTemplate jdbcTemplate; 
	
	 @Resource
	 private EasyUiDTO easyUiDto;
    
	/**
	 * 根据表名获取表中所有数据
	 * @param request
	 * @param response
	 * @return
	 * @throws SQLException
	 */
	/*@SuppressWarnings("null")
	@RequestMapping(value="getData11.do")  
    public @ResponseBody DataGrid getData11(HttpServletRequest request,HttpServletResponse response) throws SQLException{  
		
		String page = request.getParameter("page"); 
		String rows = request.getParameter("rows");  
		String create_user_id = request.getParameter("create_user_id"); 
		ArrayList<HistoricalData> list = new ArrayList<>();
		String sql = ""; 
		sql+="select * from	business_cloud_customer_dimension where	id in "
				+ "(select	max(id)	from business_cloud_customer_dimension	where 1 = 1 group by customer_code) ";
		sql+=DbQuerySql.limit(page, rows);
		Statement stmt = jdbcTemplate.getDataSource().getConnection().createStatement();  
        ResultSet rs = stmt.executeQuery(sql);
        String uuid =null;
      
        while(rs.next()){
        	uuid = rs.getString("uuid");
        	String customer_name = rs.getString("customer_name");
        	String sql1=" select * from  business_cloud_customer_dimension_child1 "
        			+ "where uuid ='"+uuid+"'";
        	Statement stmt1 = jdbcTemplate.getDataSource().getConnection().createStatement();  
            ResultSet rs1 = stmt1.executeQuery(sql1);
            while(rs1.next()) {
            	HistoricalData hd = new HistoricalData();
            	hd.setCompetitor(rs1.getString("competitor"));
            	hd.setCustomer(customer_name);
            	hd.setProducts(rs1.getString("products"));
            	hd.setSalesVolume(rs1.getString("supplier_procurement"));
            	hd.setCompetitiveEdge(rs1.getString("competitive_edge"));
            	hd.setWeakness(rs1.getString("competitive_disadvantage"));
            	hd.setRemark(rs1.getString("remark"));
            	list.add(hd);
            }
        }
        DataGrid dg = new DataGrid(list.size(), list); 
        return dg;
	}
	@RequestMapping(value="getCompetitorText.do")  
    public @ResponseBody String getCompetitorText(HttpServletRequest request,HttpServletResponse response) throws SQLException{  
		
		String competitor_value = request.getParameter("competitor_value"); 
		String competitor_text = null;
		String sql = "select competitor from  business_cloud_competitor_information  where id='"+competitor_value+"'";
		Statement stmt = jdbcTemplate.getDataSource().getConnection().createStatement();  
        ResultSet rs = stmt.executeQuery(sql);
        while(rs.next()){
        	competitor_text = rs.getString("competitor"); 
        }
        return competitor_text;
	}*/
	
	@RequestMapping(value="getData.do")  
    public @ResponseBody EasyUiDataGrid getData(HttpServletRequest request,HttpServletResponse response) throws SQLException{  
		
		String page = request.getParameter("page"); 
		String rows = request.getParameter("rows");
 
		String query_competitor = request.getParameter("query_competitor"); 
		String query_customer = request.getParameter("query_customer"); 
		String sql="" ;
		    sql += " SELECT" 
		    	+" business_cloud_competitor_information.competitor, "
		    	+" business_cloud_customer_dimension.customer_name, "
	    		+" business_cloud_customer_dimension_child1.products, "
	    		+" business_cloud_customer_dimension_child1.supplier_procurement, "
	    		+" business_cloud_customer_dimension_child1.competitive_disadvantage, "
	    		+" business_cloud_customer_dimension_child1.competitive_edge, "
	    		+" business_cloud_customer_dimension_child1.remark, "
	    		+" business_cloud_customer_dimension.uuid "
			    +" FROM "
				+" business_cloud_customer_dimension "
			    +" LEFT JOIN business_cloud_customer_dimension_child1 ON business_cloud_customer_dimension.uuid = business_cloud_customer_dimension_child1.uuid "
			    +" LEFT JOIN business_cloud_competitor_information ON business_cloud_competitor_information.id = business_cloud_customer_dimension_child1.competitor "   
			    +" WHERE "
				+" business_cloud_customer_dimension.id = ( "
				+" SELECT "
				+" max( "
				+" business_cloud_customer_dimension1.id "
				+" ) AS id "
				+" FROM "
				+" business_cloud_customer_dimension AS business_cloud_customer_dimension1 "
				+" WHERE "
				+" business_cloud_customer_dimension1.customer_code = business_cloud_customer_dimension.customer_code "
				+" ) "  
		    	+ DbQuerySql.andALikeB("business_cloud_competitor_information.competitor", query_competitor)
		        + DbQuerySql.andALikeB("business_cloud_customer_dimension.customer_name", query_customer);
		    sql+=DbQuerySql.limit(page, rows);
		  
		    EasyUiDataGrid edg= easyUiDto.getEasyUiDataGrid(sql,sql);
    		return  edg ;
	}
 }
